package top.aoyudi.service.serviceImpl;

import org.apache.commons.lang3.StringUtils;
import top.aoyudi.service.IMySqlService;

import java.sql.*;
import java.util.*;

public class MySqlServiceImpl implements IMySqlService {

    public List<String> getTableFields(Statement statement, String tableName) throws SQLException {
//        1.执行SQL的对象去执行SQL，返回结果集
        String fieldSql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = '" + tableName + "'";
//        2.获取表字段
        ResultSet fieldResultSet = statement.executeQuery(fieldSql);
//        3.数据库表字段
        List<String> fieldList = new ArrayList<>();
        while (fieldResultSet.next()) {
            fieldList.add(fieldResultSet.getString(1));
        }
        fieldResultSet.close();
        return fieldList;
    }

    public Map<Integer, List<String>> getTableData(Statement statement, String tableName) throws SQLException {
        List<String> tableFields = getTableFields(statement, tableName);
        // 1.执行SQL的对象去执行SQL，返回结果集
        String dataSql = "select * from " + tableName;
        // 2.数据库表数据
        ResultSet dataResultSet = statement.executeQuery(dataSql);
        Map<Integer, List<String>> dataMap = new HashMap<>();
        while (dataResultSet.next()) {
            List<String> rowDataList = new ArrayList<>();
            for (int i = 1; i <= tableFields.size(); i++) {
                rowDataList.add(dataResultSet.getString(i));
            }
            dataMap.put(dataResultSet.getRow(), rowDataList);
        }
        dataResultSet.close();
        return dataMap;
    }

    public boolean createTable(Statement statement, String tableName) {
        // 创建表时增加一个id字段
        String creatTableSql = "CREATE TABLE " + tableName + " (id int NOT NULL,PRIMARY KEY (id));";
        try {
            statement.execute(creatTableSql);
            return true;
        } catch (SQLException e) {
            return false;
        }
    }

    public boolean addColumn(Statement statement, String tableName, List<String> columnList) {
        // ALTER TABLE user ADD COLUMN age varchar(255);
        try {
            String addColumnSql;
            for (String columnName : columnList) {
                addColumnSql = "ALTER TABLE "+tableName+" ADD COLUMN "+columnName+" varchar(255);";
               if (!statement.execute(addColumnSql)){
                   return false;
               }
            }
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public boolean insertInToTable(Statement statement, String tableName,List<String> columnList, List<String> rowDataList) throws SQLException {
        String column = StringUtils.join(columnList, ",");
        String columnStr="("+column+")";
        String rowData = StringUtils.join(rowDataList, ",");
        String rowDataStr= "("+rowData+")";
        String sql="insert into "+tableName+" "+columnStr+" values "+rowDataStr;
        return statement.executeUpdate(sql) > 0;
    }
}
